CREATE TABLE [dbo].[PromotionPay](
	[PromotionPayId] [int] IDENTITY(1,1) NOT NULL,
	[PromotionId] [int] NOT NULL,
	[DatePay] [datetime] NOT NULL,
	[Amount] [money] NOT NULL,
	[CreditCardNo] [varchar](20) NOT NULL,
 CONSTRAINT [PK_PromotionPay] PRIMARY KEY CLUSTERED 
(
	[PromotionPayId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PromotionPay]  WITH CHECK ADD  CONSTRAINT [FK_PromotionPay_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionPay] CHECK CONSTRAINT [FK_PromotionPay_Promotion]
GO
ALTER TABLE PromotionPush
ADD RatePerPush MONEY not NULL default(0)
GO
UPDATE PromotionPush
SET RatePerPush = Promotion.RatePerPush
FROM Promotion
WHERE PromotionPush.PromotionId = Promotion.PromotionId
GO
ALTER TABLE PromotionPush
ALTER COLUMN RatePerPush MONEY NOT NULL
GO
ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
(
	@Token VARCHAR(255),
	@Latitude FLOAT,
	@Longtitude FLOAT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	BEGIN
		CREATE TABLE #tmpPromotions
		(
			PromotionId INT,
			RatePerPush MONEY
		)
		
		BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId, RatePerPush)
			SELECT Promotion.PromotionId, Promotion.RatePerPush
			FROM Promotion (NOLOCK) LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
						LEFT JOIN ProximityRadius (NOLOCK) ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
			WHERE /*(NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = Promotion.PromotionId)))
				AND*/ (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))		
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Promotion.StatusCode = 'ACT')
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
								ISNULL(vwReview.CountReviews, 0) AS Reviews, 15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions INNER JOIN Promotion (NOLOCK) ON #tmpPromotions.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN (SELECT BusinessId, 
													COUNT(*) AS CountReviews, AVG(CONVERT(FLOAT, Rating)) AS AvgRating 
													FROM BusinessReview (NOLOCK)
													GROUP BY BusinessId) AS vwReview ON Business.BusinessId = vwReview.BusinessId
							FOR XML PATH ('Promotion'), ROOT('Promotions'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
			SELECT PromotionId, @UserId, GETDATE(), @Latitude, @Longtitude, RatePerPush
			FROM #tmpPromotions
			WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
		COMMIT TRANSACTION
		
		DROP TABLE #tmpPromotions
	END
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO

